<?php
#!/bin/bash

/**
 * User: hzbskak
 * Date: 2019/12/31 - 11:11
 */

if( $_FILES)
{
    require_once "DB.php";
    require_once "PHPExcel/PHPExcel.php";
    $db = new DB();

    ini_set('memory_limit', '-1'); //设置程序运行的内存
    ini_set('max_execution_time',0); //设置程序的执行时间,0为无上限
    ini_set('display_errors', 1); // 开启报错
    error_reporting( E_ALL ); //设置报错等级

    if (!empty($_FILES['excel']['name'])) {
        $fileName = $_FILES['excel']['name'];    //得到文件全名
        $dotArray = explode('.', $fileName);    //把文件名安.区分，拆分成数组
        $type = end($dotArray);

        //取数组最后一个元素，得到文件类型
        $uploaddir = date('Y-m-d') . '/';//设置文件保存目录 注意包含
        if (!file_exists($uploaddir)) {
            mkdir($uploaddir, 0777, true);
        }

        $path = $uploaddir . md5(uniqid(rand())) . '.' . $type; //产生随机文件名
        //$path = "images/".$fileName; //客户端上传的文件名；
        //下面必须是tmp_name 因为是从临时文件夹中移动
        move_uploaded_file($_FILES['excel']['tmp_name'], $path); //从服务器临时文件拷贝到相应的文件夹下

        $file_path = $path;
        if (!file_exists($path)) {
            $ret['res'] = "0";
            $ret['msg'] = "上传文件丢失!" . $_FILES['excel']['error'];
            echoJson( $ret);
        }

        //文件的扩展名
        $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
        // xls xlsx 上传
        if($ext != 'csv')
        {
            if ($ext == 'xlsx') {
                $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                $objPHPExcel = $objReader->load($file_path, 'utf-8');
            } elseif ($ext == 'xls') {
                $objReader = \PHPExcel_IOFactory::createReader('Excel5');
                $objPHPExcel = $objReader->load($file_path, 'utf-8');
            }
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数

            // 最大列数
            $sql2 = "select count(1) from information_schema.COLUMNS where table_name = 'ht_price'";
            $column_max = $db->getRow( $sql2)['count(1)'];
            // 表的字段key value
            $sql3 = "select column_name as k,column_comment as v from information_schema.columns where table_name='ht_price'";
            $kv = $db->getAll( $sql3);
            // 表的自增主键
            $sql4 = "SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='ht_price' AND constraint_name='PRIMARY'";
            $primary = $db->getRow( $sql4)['column_name'];

            // 循环列 输出 a-z-zz
            $list = [];
            for ($i = 0; $i <= $column_max-1; $i++) {
                $y = ($i / 26);
//    echo $i,"=>";
                if ($y >= 1) {
                    $y = intval($y);
                    $a = chr($y+64);
                    $b = chr($i-$y*26 + 65);
                    $list [] = $a . $b;
                } else {
                    $c = chr($i+65);
                    $list [] = $c;
                }
//    echo "\r\n";
            }
            // 重组kv数组，追加a-z-zz
            // 并且把不需要写入的字段去掉，
            foreach ( $kv as $k => &$v)
            {
                // 删掉不需要添加的数
                if( $v['k'] == $primary || $v['k'] == 'add_time' || $v['k'] == 'edit_time')
                {
                    unset( $kv[$k]);
                }
                $v['col'] = $list[$k];
            }
            $keys = array_column( $kv, 'k');
            $keys_list = implode( ',', $keys);
            $ar = array();
            $i = 0;
            $importRows = 0;
            $data = [];
            // 插入语句
            $sql = "insert into price_copy (".$keys_list.") values ";
            for ($j = 2; $j <= $highestRow; $j++) {
                $importRows++;
                $sql .= "(";
                foreach ( $kv as $k => $v)
                {
                    // 去掉不需要上传的字段数据
                    if( $v['k'] != $primary && $v['k'] != 'add_time' && $v['k'] != 'edit_time')
                    {
                        // 日期转unix时间戳
                        if( $v['k'] == 'date')
                        {
                            $sql .= "'".strtotime( $objPHPExcel->getActiveSheet()->getCell($v['col'].$j)->getValue())."',";
                        }
                        else
                        {
                            $sql .= "'".$objPHPExcel->getActiveSheet()->getCell($v['col'].$j)->getValue()."',";
                        }
                    }
                }
                $sql = substr( $sql, 0, strlen($sql) - 1);
                $sql .= "),";
            }
            $sql = substr( $sql, 0, strlen($sql) - 1);
            $ret = $db->query( $sql);
            echoJson( $ret);
        }
        else
        {
            setlocale(LC_ALL,array('zh_CN.gbk','zh_CN.gb2312','zh_CN.gb18030')); // 防乱码
            $handle = fopen($file_path,'r');
            if(!$handle){
                echoJson('文件打开失败');
            }

            $i = 0;
            $j = 0;
            $result = [];
            // 第二行
            $offset = 1;
            // 读几行
            $line = 0;
            while($data = fgetcsv($handle)){
                //小于偏移量则不读取,但$i仍然需要自增
                // 从第二行开始读
                if($i < $offset && $offset){
                    $i++;
                    continue;
                }
                //大于读取行数则退出
                if($i > $line && $line){
                    break;
                }

                foreach ($data as $key => $value) {
                    $content = @iconv("gbk","utf-8//TRANSLIT//IGNORE",$value);//转化编码
                    $result[$j][] = $content;
                }
                $i++;
                $j++;
            }
            // 最大列数
            $sql2 = "select count(1) from information_schema.COLUMNS where table_name = 'ht_price'";
            $column_max = $db->getRow( $sql2)['count(1)'];
            // 表的字段key value
            $sql3 = "select column_name as k,column_comment as v from information_schema.columns where table_name='ht_price'";
            $kv = $db->getAll( $sql3);
            // 表的自增主键
            $sql4 = "SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='ht_price' AND constraint_name='PRIMARY'";
            $primary = $db->getRow( $sql4)['column_name'];
            // 循环列 输出 a-z-zz
            $list = [];
            for ($i = 0; $i <= $column_max-1; $i++) {
                $y = ($i / 26);
            //    echo $i,"=>";
                if ($y >= 1) {
                    $y = intval($y);
                    $a = chr($y+64);
                    $b = chr($i-$y*26 + 65);
                    $list [] = $a . $b;
                } else {
                    $c = chr($i+65);
                    $list [] = $c;
                }
            //    echo "\r\n";
            }
            // 重组kv数组，追加a-z-zz
            // 并且把不需要写入的字段去掉，
            foreach ( $kv as $k => &$v)
            {
                $v['col'] = $list[$k];
            }
            $keys = array_column( $kv, 'k');
            $keys_list = implode( ',', $keys);
            $size = 500; //批量插入500条数据
            $chunkData = array_chunk($result , $size);
            $count = count($chunkData);
//            echoJson( $kv); // 62
//            echoJson( $chunkData[0][0]);
            $len_result = count( $result);
            mysqli_query( $db->link, 'begin');
            try
            {
                for ($i=0; $i < $count; $i++) { // 100
                    // 每500条数据执行一次sql
                    $sql = "insert into price_copy (".$keys_list.") values ";
                    for( $j=0; $j < $size; $j++) // 500
                    {
                        $sql .= "(";
                        for ( $l=0;$l< $column_max;$l++) // 60
                        {
                            // 过滤主键、不需要添加或者需要设置字段值得字段
                            if( $kv[$l]['k'] == $primary)
                            {
                                $sql .= "null,";
                            }
                            elseif( $kv[$l]['k'] == 'date')
                            {
                                $sql .= "'".strtotime( $chunkData[$i][$j][$l])."',";
                            }
                            elseif( $kv[$l]['k'] == 'add_time')
                            {
                                $sql .= "'".date('Y-m-d H:i:s')."',";
                            }
                            elseif( $kv[$l]['k'] == 'edit_time')
                            {
                                $sql .= "'".date('Y-m-d H:i:s')."',";
                            }
                            else
                            {
                                // 不需要过滤或者重写值，直接取
                                $sql .= "'".@$chunkData[$i][$j][$l]."',";
                            }
                        }
                        $sql = substr( $sql, 0, strlen($sql) - 1);
                        $sql .= "),";
                    }
                    $sql = substr( $sql, 0, strlen($sql) - 1);
                    $ret = $db->query( $sql);
                }
                mysqli_query( $db->link, 'commit');
            }
            catch ( Exception $e)
            {
                echoJson( $e->getMessage());
                mysqli_query( $db->link, 'rollback');
            }
            mysqli_query( $db->link, 'end');

            fclose($handle);
            echoJson( $ret);
        }

    } else {
        $ret['res'] = "0";
        $ret['msg'] = "上传文件失败!";
        echoJson( $ret);
    }

}

function echoJson($arr)
{
    echo json_encode($arr);
    exit;
}
?>

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<form action="import.php" method="post" enctype="multipart/form-data">
    <input type="file" name="excel">
    <input type="submit">上传
</form>
</body>
</html>




























